﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
/// <summary>
/// Summary description for Database
/// </summary>
public class Database
{
	public Database()
	{
		//
		// TODO: Add constructor logic here
		//
	}

    public static DataTable getTable(string query)
    {
        DataTable dt = new DataTable();
        try
        {
            SqlConnection con = ConnectionPool.getConnection();
            SqlDataAdapter dap = new SqlDataAdapter(query, con);
            dap.Fill(dt);
        }
        catch (SqlException ex)
        {
            return null;
            throw new Exception(ex.Message);
        }
        return dt;
    }

    public static DataTable getTableProcedure(string procedurename, string[] paras, string[] values)
    {
        DataTable dt = new DataTable();
        try
        {
            SqlConnection con = ConnectionPool.getConnection();
            SqlCommand com = new SqlCommand(procedurename, con);
            com.Connection = con;
            com.CommandType = CommandType.StoredProcedure;
            for (int i = 0; i < paras.Length; i++)
            {
                com.Parameters.AddWithValue(paras[i], values[i]);
            }
            SqlDataAdapter dap = new SqlDataAdapter(com);
            dap.Fill(dt);

        }
        catch (SqlException ex)
        {
            dt = null;
            throw new Exception(ex.Message);
        }
        return dt;
    }
    public static SqlDataReader getReader(string query)
    {
        try
        {
            SqlConnection con = ConnectionPool.getConnection();
            if (con.State == ConnectionState.Open)
                con.Close();
            con.Open();
            SqlCommand com = new SqlCommand(query, con);
            return com.ExecuteReader();
            con.Close();
        }
        catch (SqlException ex)
        {
            return null;
            throw new Exception(ex.Message);
        }

    }

    public static SqlDataReader getReaderProcedure(string procedurename, string[] paras, string[] values)
    {
        try
        {
            SqlConnection con = ConnectionPool.getConnection();
            SqlCommand com = new SqlCommand(procedurename, con);
            if (con.State == ConnectionState.Open)
                con.Close();
            con.Open();
            com.CommandType = CommandType.StoredProcedure;
            for (int i = 0; i < paras.Length; i++)
            {
                com.Parameters.AddWithValue(paras[i], values[i]);
            }
            return com.ExecuteReader();
            con.Close();
        }
        catch (SqlException ex)
        {
            return null;
            throw new Exception(ex.Message);
        }
    }

    public static object getScalar(string query)
    {
        try
        {
            SqlConnection con = ConnectionPool.getConnection();
            if (con.State == ConnectionState.Open)
                con.Close();
            con.Open();
            SqlCommand com = new SqlCommand(query, con);
            return com.ExecuteScalar();
            con.Close();
        }
        catch (SqlException ex)
        {
            return null;
            throw new Exception(ex.Message);
        }
    }

}
